package cn.spider.service;

import cn.spider.db.C3P0Utils;
import cn.spider.pojo.HotTopic;
import cn.spider.pojo.Rpt1Vo;
import cn.spider.pojo.Rpt2Vo;
import cn.spider.pojo.Rpt3Vo;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by laoYang on 2018/4/22.
 */
public class ReportServiceImpl implements IReportService {


//    QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());


    public List<List<Object>> reportLatelyN() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());

        String sql = "SELECT DATE_FORMAT(d.create_time,'%Y-%m-%d') as cday," +
                "count(DISTINCT d.aid) as cnts " +
                "FROM t_tb_detail d " +
                "WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(DATE_FORMAT(create_time,'%Y-%m-%d')) " +
                "GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ORDER BY cday";


        try {
            return runner.query(sql, new ResultSetHandler<List<List<Object>>>() {
                public List<List<Object>> handle(ResultSet rs) throws SQLException {

                    List<List<Object>> list = new ArrayList<List<Object>>();

                    while (rs.next()) {
                        List<Object> rowData = new ArrayList<Object>();

                        String day = rs.getString("cday");
                        Integer cnts = rs.getInt("cnts");

                        rowData.add(day);
                        rowData.add(cnts);

                        list.add(rowData);
                    }
                    return list;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Rpt2Vo> reportCurrentWeek() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT classfiy_art," +
                "sum(case when t2.wk=1 then cnts else 0 end) as wk1," +
                "sum(case when t2.wk=2 then cnts else 0 end) as wk2," +
                "sum(case when t2.wk=3 then cnts else 0 end) as wk3," +
                "sum(case when t2.wk=4 then cnts else 0 end) as wk4," +
                "sum(case when t2.wk=5 then cnts else 0 end) as wk5," +
                "sum(case when t2.wk=6 then cnts else 0 end) as wk6," +
                "sum(case when t2.wk=7 then cnts else 0 end) as wk7 " +
                "from (" +
                "SELECT DAYOFWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')- INTERVAL 1 DAY) wk,classfiy_art, count(DISTINCT aid) as cnts " +
                "FROM t_tb_detail " +
                "WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')- INTERVAL 1 DAY) = YEARWEEK(now()- INTERVAL 1 DAY)" +
                "GROUP BY DAYOFWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')- INTERVAL 1 DAY),classfiy_art ORDER BY classfiy_art,wk) as t2  group by t2.classfiy_art";
        try {
            return runner.query(sql, new ResultSetHandler<List<Rpt2Vo> >() {
                public List<Rpt2Vo>  handle(ResultSet rs) throws SQLException {
                    List<Rpt2Vo> list = new ArrayList<Rpt2Vo>();

                    while (rs.next()) {

                        String name = rs.getString("classfiy_art");

                        Rpt2Vo vo1 = new Rpt2Vo();
                        vo1.setName(name);
                        vo1.setData(Arrays.asList(
                                rs.getInt("wk1"),
                                rs.getInt("wk2"),
                                rs.getInt("wk3"),
                                rs.getInt("wk4"),
                                rs.getInt("wk5"),
                                rs.getInt("wk6"),
                                rs.getInt("wk7")
                        ));
                        list.add(vo1);

                    }

                    return list;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    public List<Rpt3Vo> reportLastWeek() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT classfiy_art," +
                "sum(case when t2.wk=1 then cnts else 0 end) as wk1," +
                "sum(case when t2.wk=2 then cnts else 0 end) as wk2," +
                "sum(case when t2.wk=3 then cnts else 0 end) as wk3," +
                "sum(case when t2.wk=4 then cnts else 0 end) as wk4," +
                "sum(case when t2.wk=5 then cnts else 0 end) as wk5," +
                "sum(case when t2.wk=6 then cnts else 0 end) as wk6," +
                "sum(case when t2.wk=7 then cnts else 0 end) as wk7 " +
                "from (" +
                "SELECT DAYOFWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')- INTERVAL 1 DAY) wk,classfiy_art, count(DISTINCT aid) as cnts " +
                "FROM t_tb_detail " +
                "WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')- INTERVAL 1 DAY) = YEARWEEK(now()- INTERVAL 1 DAY) - 1 " +
                "GROUP BY DAYOFWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')- INTERVAL 1 DAY),classfiy_art ORDER BY classfiy_art,wk) as t2  group by t2.classfiy_art";
        try {
            return runner.query(sql, new ResultSetHandler<List<Rpt3Vo> >() {
                public List<Rpt3Vo>  handle(ResultSet rs) throws SQLException {
                    List<Rpt3Vo> list = new ArrayList<Rpt3Vo>();

                    while (rs.next()) {

                        String name = rs.getString("classfiy_art");

                        Rpt3Vo vo = new Rpt3Vo();
                        vo.setName(name);
                        vo.setData(Arrays.asList(
                                rs.getInt("wk1"),
                                rs.getInt("wk2"),
                                rs.getInt("wk3"),
                                rs.getInt("wk4"),
                                rs.getInt("wk5"),
                                rs.getInt("wk6"),
                                rs.getInt("wk7")
                        ));
                        list.add(vo);
                    }
                    return list;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }

    }

    public List<Rpt1Vo> reportAll() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT d.classfiy_art AS name,count(DISTINCT aid) AS value FROM t_tb_detail d GROUP BY d.classfiy_art";
        try {
            return runner.query(sql, new BeanListHandler<Rpt1Vo>(Rpt1Vo.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<String> topCategory() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT t.classfiy_art from t_tb_detail t group by t.classfiy_art ORDER BY t.classfiy_art ";
        try {
            return runner.query(sql, new ResultSetHandler<List<String>>() {
                public List<String> handle(ResultSet rs) throws SQLException {
                    List<String> list = new ArrayList<String>();

                    while (rs.next()) {
                        list.add(rs.getString("classfiy_art"));
                    }
                    return list;
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<HotTopic> getCurrentTop20() {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql ="select * from t_tb_hottopic order by ymdh_time desc, id asc limit 20";
        try {
            return runner.query(sql, new BeanListHandler<HotTopic>(HotTopic.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
}
